/**
 * <pre>
 * Copyright:		Copyright(C) 2011-2012, ketayao.com
 * Filename:		com.ketayao.dao.imp.AbstractPageDaoImp.java
 * Class:			AbstractPageDaoImp
 * Date:			2012-4-12
 * Author:			<a href="mailto:ketayao@gmail.com">ketayao</a>
 * Version          1.1.0
 * Description:		
 *
 * </pre>
 **/

package com.ketayao.dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.util.StringUtils;

import com.ketayao.dao.PageInfo;
import com.ketayao.dao.jdbc.JdbcTemplateProxy;

/**
 * @author <a href="mailto:ketayao@gmail.com">ketayao</a> Version 1.1.0
 * @created 2012-4-12 下午12:31:37
 */

public abstract class AbstractDaoImp<T> extends SimpleJdbcDaoSupport {

    protected final Log logger = LogFactory.getLog(getClass());

    // /**
    // * 使用了游标，减少数据的访问
    // * @param pageInfo
    // * @param sql
    // * @param rowMapper
    // * @return
    // */
    // public List<T> findPage(PageInfo pageInfo, String sql, RowMapper<T> rowMapper) {
    // List<T> result = new ArrayList<T>();
    // Connection connection = null;
    // PreparedStatement pstat = null;
    // ResultSet rs = null;
    // try {
    //
    // if (this.getJdbcTemplate() instanceof JdbcTemplateProxy) {
    // JdbcTemplateProxy jdbcTemplateProxy = (JdbcTemplateProxy) this.getJdbcTemplate();
    //
    // connection = jdbcTemplateProxy.getSlaveDataSource().getConnection();
    // } else {
    // connection = this.getJdbcTemplate().getDataSource().getConnection();
    // }
    //
    // pstat = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    // rs = pstat.executeQuery();
    //
    // // 使用游标来得到总条数
    // rs.last();
    // int totalCount = rs.getRow();
    //
    // pageInfo.setTotalRec(totalCount);
    //
    // int startIndex = (pageInfo.getPageIndex() - 1) * pageInfo.getPageSize() + 1;
    //
    // if (totalCount > 0) {
    // rs.absolute(startIndex);
    //
    // int index = 0;
    // do {
    // result.add(rowMapper.mapRow(rs, index++));
    // } while (rs.next() && index < pageInfo.getPageSize());
    // }
    //
    // } catch (Exception e) {
    // logger.error("find page is ERROR, from sql:[" + sql + "]\n" + e.getMessage());
    // } finally {
    // try {
    // if (rs != null) {
    // rs.close();
    // }
    // if (pstat != null) {
    // pstat.close();
    // }
    // if (connection != null) {
    // connection.close();
    // }
    // } catch (SQLException e) {
    // logger.error("close Connection or PreparedStatement, ResultSet Error." + e.getMessage());
    // }
    // }
    //
    // return result;
    // }

    /**
     * Override findPage by lazy_p@163.com
     * @param pageInfo
     * @param sql
     * @param rowMapper
     * @return
     */
    public List<T> findPage(PageInfo pageInfo, String sql, RowMapper<T> rowMapper) {

        String selectCountSql = this.getSelectCount(sql);
        int totalCount = this.getSimpleJdbcTemplate().queryForInt(selectCountSql);
        pageInfo.setTotalRec(totalCount);

        int startIndex = (pageInfo.getPageIndex() - 1) * pageInfo.getPageSize();
        if (startIndex < 0) {// 查询单条数据的时候，可能会出现
            startIndex = 0;
        }
        sql = sql + " LIMIT " + pageInfo.getPageSize() + " OFFSET " + startIndex;
        if (logger.isDebugEnabled()) {
            logger.info("sql:" + sql);
        }

        return this.getSimpleJdbcTemplate().query(sql, rowMapper);
    }

    private String getSelectCount(String sql) {
        if (StringUtils.hasLength(sql)) {
            int index = sql.indexOf(" FROM ");
            if (index == -1) {
                index = sql.indexOf(" from ");
            }
            if (index == -1) {
                throw new RuntimeException("SELECT SQL ERROR.");
            }

            String suffix = sql.substring(index);
            String selectCountSql = "SELECT COUNT(*) " + suffix;
            return selectCountSql;
        } else {
            throw new RuntimeException("SELECT SQL ERROR.");
        }
    }
}
